package com.iplant.base.utils.sql;

import com.iplant.base.po.Pagination;
import com.iplant.base.po.enums.DBEnumType;
import com.iplant.base.utils.general.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.transaction.support.TransactionSynchronizationManager;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

@Slf4j
public class ShrisJdbcTemplate extends NamedParameterJdbcTemplate {

    public ShrisJdbcTemplate(JdbcOperations classicJdbcTemplate, DBEnumType wDBType) {

        super(classicJdbcTemplate);
        DBType = wDBType;
        mDataSource = DBDruidHelper.getInstance().getDataSource(wDBType);
        // TODO Auto-generated constructor stub
    }

    private DataSource mDataSource;

    public ShrisJdbcTemplate(DataSource dataSource, DBEnumType wDBType) {
        super(dataSource);
        mDataSource = dataSource;
        DBType = wDBType;
        // TODO Auto-generated constructor stub
    }

    public DBEnumType DBType;

    public List<Map<String, Object>> queryForList(String wSQL, Map<String, Object> wParamMap, Pagination wPagination) {

        if (wPagination == null) {
            return super.queryForList(wSQL, wParamMap);
        }

        wSQL.trim();

        if (wSQL.endsWith(";")) {
            wSQL = wSQL.substring(0, wSQL.length() - 1);
        }
        if (wPagination.getPageSize() > 0 && wPagination.getPageSize() <= Pagination.DEFAULT_MAX_PAGE_) {
            this.PrevQueryForList(wSQL, wParamMap, wPagination);

            if (wPagination.getTotalCount() <= 0) {
                return new ArrayList<Map<String, Object>>();
            }
        }
        wSQL = ShrisJdbcTemplate.SqlPageSql(wSQL, DBType, wPagination.getSort(), wPagination.getSortType(),
                wPagination.getPageSize(), wPagination.getPageIndex()).toString();

        return super.queryForList(wSQL, wParamMap);
    }


    public List<Map<String, Object>> queryForList(String wSQL, Map<String, Object> wParamMap, Pagination pagination, String wTableName, List<String> wTableNames) {
        if (StringUtils.isEmpty(wTableName) || wTableNames == null || wTableNames.size() == 0 || !wSQL.contains(wTableName))
            return queryForList(wSQL, wParamMap, pagination);

        wTableNames.remove(wTableName);
        wTableNames.removeIf(p -> StringUtils.isEmpty(p));

        if (wTableNames.size() == 0) {
            return queryForList(wSQL, wParamMap, pagination);
        }

        List<String> wSql = new ArrayList<String>();
        for (String wTbName : wTableNames) {
            wSql.add("(" + wSQL.replace(wTableName, wTbName) + ")");
        }

        wSQL = StringUtils.Join(" union all ", wSql);

        return queryForList(wSQL, wParamMap, pagination);
    }


    public synchronized int Execute(List<String> wSqlList) {

        int wResult = 0;
        if (wSqlList == null || wSqlList.size() == 0) {
            return wResult;
        }

        wSqlList.removeIf(p -> StringUtils.isEmpty(p));

        if (wSqlList.size() == 0) {
            return wResult;
        }

        Connection connection = null;
        try {
            TransactionSynchronizationManager.initSynchronization();
            connection = this.getConnection();

            connection.setAutoCommit(false);

            Statement stat = connection.createStatement();

            for (String String : wSqlList) {

                stat.addBatch(String);
            }
            int[] wUpdateRows = stat.executeBatch();
            // 需要操作数据库的两个insert，或者提供回调给业务开发人员
            connection.commit();

            wResult = Arrays.stream(wUpdateRows).sum();
        } catch (SQLException e) {
            wResult = -1;
            log.error("BaseDAO Execute commit", e);
            try {
                connection.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                log.error("BaseDAO Execute rollback", e1);
            }
        } finally {
            try {
                TransactionSynchronizationManager.clearSynchronization();
            } catch (IllegalStateException e) {
                log.error("BaseDAO Execute clearSynchronization", e);
            }
            try {
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                log.error("BaseDAO Execute setAutoCommit", e);
            }
            try {
                connection.close();
            } catch (SQLException e) {
                log.error("BaseDAO Execute close", e);
            }
        }

        return wResult;
    }

    private void PrevQueryForList(String wSQL, Map<String, Object> wParamMap, Pagination wPagination) {

        if (wPagination.getPageFuzzy() == 1) {

            String wSqlTemp = " explain " + wSQL;
            List<Map<String, Object>> wResultList = super.queryForList(wSqlTemp, wParamMap);
            int wCount = 0;
            for (Map<String, Object> wMap : wResultList) {

                if (wMap == null || !wMap.containsKey("rows"))
                    continue;
                wCount = StringUtils.parseInt(wMap.get("rows"));

                if (wCount > wPagination.getTotalCount())
                    wPagination.setTotalCount(wCount);
            }
        } else {
            String wSqlTemp = "SELECT COUNT(0) as ItemCount FROM (" + wSQL + ") CountTable;";
            Map<String, Object> wResult = super.queryForMap(wSqlTemp, wParamMap);

            if (wResult != null && wResult.containsKey("ItemCount")) {

                wPagination.setTotalCount(StringUtils.parseInt(wResult.get("ItemCount")));
            }
        }
    }

    public static StringBuilder SqlPageSql(String strSql, DBEnumType wDBType, String sort, boolean isAsc, int pageSize,
                                           int pageIndex) {
        StringBuilder sb = null;
        switch (wDBType) {
            case Default:

                break;
            case MySQL:
                sb = SqlPageMySql(strSql, sort, isAsc, pageSize, pageIndex);
                break;
            case SQLServer:
                sb = SqlPageSqlServer(strSql, sort, isAsc, pageSize, pageIndex);
                break;
            case Oracle:
                break;
            case Access:
                break;
            default:
                break;
        }
        return sb;
    }

    public static StringBuilder SqlPageSql(String strSql, DBEnumType wDBType, String sort, String sortType,
                                           int pageSize, int pageIndex) {

        if (StringUtils.isEmpty(sortType)) {
            sortType = "desc";
        }

        boolean isAsc = sortType.equalsIgnoreCase("ASC");

        return ShrisJdbcTemplate.SqlPageSql(strSql, wDBType, sort, isAsc, pageSize, pageIndex);
    }

    private static StringBuilder SqlPageSqlServer(String strSql, String sort, boolean isAsc, int pageSize,
                                                  int pageIndex) {
        StringBuilder sb = new StringBuilder();
        if (pageIndex <= 0) {
            pageIndex = 1;
        }
        int num = (pageIndex - 1) * pageSize;
        int num1 = (pageIndex) * pageSize;
        String OrderBy = "";

        String OrderBy_M = "";
        if (StringUtils.isNotEmpty(sort)) {
            if (sort.toUpperCase().indexOf("ASC") + sort.toUpperCase().indexOf("DESC") > 0) {
                OrderBy = " ORDER BY " + sort;
            } else {
                OrderBy = " ORDER BY " + sort + " " + (isAsc ? "ASC" : "DESC");
            }
            OrderBy_M = OrderBy;
        } else {
            OrderBy = "ORDERE BY (SELECT 0)";
        }

        if (pageSize <= 0 || pageSize >= Pagination.DEFAULT_MAX_PAGE_) {
            sb.append(strSql + OrderBy_M);
        } else {
            sb.append("SELECT * FROM (SELECT ROW_NUMBER() Over (" + OrderBy + ")");
            sb.append(" AS ROWNUM, * From (" + strSql + ") t ) AS N WHERE ROWNUM > " + num + " AND ROWNUM <= " + num1 + "");
        }
        return sb;
    }

    private static StringBuilder SqlPageMySql(String strSql, String sort, boolean isAsc, int pageSize, int pageIndex) {
        StringBuilder sb = new StringBuilder();
        if (pageIndex <= 0) {
            pageIndex = 1;
        }

        String OrderBy = "";

        if (StringUtils.isNotEmpty(sort)) {
            if (sort.toUpperCase().indexOf("ASC") + sort.toUpperCase().indexOf("DESC") > 0) {
                OrderBy = " ORDER BY " + sort;
            } else {
                OrderBy = " ORDER BY " + sort + " " + (isAsc ? "ASC" : "DESC");
            }
        }
        if (pageSize <= 0 || pageSize >= Pagination.DEFAULT_MAX_PAGE_) {
            sb.append(strSql + OrderBy);
        } else {
            sb.append(strSql + OrderBy + " limit " + ((pageIndex - 1) * pageSize) + "," + pageSize);
        }
        return sb;
    }

    public Connection getConnection() throws SQLException {
        if (mDataSource == null) {
            return null;
        }
        return mDataSource.getConnection();

    }
}
